/*
 * $Id: CategoryPlanningItemManager.java 1667 2011-08-24 10:06:20Z abie $
 * 
 * Copyright (c) 2001-2008 Accentrix Company Limited. All Rights Reserved.
 * 
 * Accentrix Company Limited. ("Accentrix") retains copyright on all text, source
 * and binary code contained in this software and documentation. Accentrix grants
 * Licensee a limited license to use this software, provided that this copyright
 * notice and license appear on all copies of the software. The software source
 * code is provided for reference purposes only and may not be copied, modified 
 * or distributed.
 * 
 * THIS SOFTWARE AND DOCUMENTATION ARE PROVIDED "AS IS," WITHOUT ANY WARRANTY OF
 * ANY KIND UNLESS A SEPARATE WARRANTIES IS PURCHASED FROM ACCENTRIX AND REMAINS
 * VALID.  ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES,
 * INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
 * PURPOSE OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. ACCENTRIX SHALL NOT BE LIABLE
 * FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING OR MODIFYING THE
 * SOFTWARE OR ITS DERIVATIVES.
 * 
 * IN NO EVENT WILL ACCENTRIX BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR
 * FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES,
 * HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE
 * USE OF OR INABILITY TO USE SOFTWARE, EVEN IF ACCENTRIX HAS BEEN ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGES.
 */
package com.lightsaber.trade.cloth.service.planfirst;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.dom4j.Attribute;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.lightsaber.trade.core.common.treegrid.GridDoc;
import com.lightsaber.trade.core.common.util.DateUtil;
import com.lightsaber.trade.core.dao.BaseDao;
import com.lightsaber.trade.core.service.HppBaseServiceImpl;
import com.lightsaber.trade.cloth.dao.category.CategoryLeafDao;
import com.lightsaber.trade.cloth.dao.category.CategoryPlannerDao;
import com.lightsaber.trade.cloth.dao.planning.CategoryPlanningItemDao;
import com.lightsaber.trade.cloth.dao.planning.Level1PlanningDao;
import com.lightsaber.trade.cloth.entity.category.Category;
import com.lightsaber.trade.cloth.entity.category.CategoryLeaf;
import com.lightsaber.trade.cloth.entity.category.CategoryNode;
import com.lightsaber.trade.cloth.entity.category.InputType;
import com.lightsaber.trade.cloth.entity.category.SourceType;
import com.lightsaber.trade.cloth.entity.planning.CategoryPlanningItem;
import com.lightsaber.trade.cloth.entity.planning.Level1Planning;
import com.lightsaber.trade.cloth.entity.planning.PlanningStatus;
import com.lightsaber.trade.cloth.service.category.CategoryNodeService;
import com.lightsaber.trade.cloth.service.category.CategoryService;
import com.lightsaber.trade.cloth.service.param.ParamService;
import com.lightsaber.trade.cloth.web.planfirst.Operator;

@Service
@Transactional
public class CategoryPlanningItemManager extends HppBaseServiceImpl<CategoryPlanningItem, String> {
    private final static Logger log = LoggerFactory.getLogger(CategoryPlanningItemManager.class);

    @Autowired
    CategoryPlanningItemDao categoryPlanningItemDao;
    @Autowired
    Level1PlanningDao level1PlanningDao;
    @Autowired
    CategoryLeafDao categoryLeafDao;
    @Autowired
    CategoryPlannerDao categoryPlannerDao;
    @Autowired
    CategoryNodeService categoryNodeService;
    @Autowired
    CategoryService categoryService;

    @Autowired
    ParamService paramService;

    @Override
    protected BaseDao<CategoryPlanningItem, String> getDao() {
        return categoryPlanningItemDao;
    }

    public CategoryPlanningItem findByPlanAndLeafAndYear(final Level1Planning level1Planning,
            final CategoryLeaf categoryLeaf, final Integer planYear) {
        return categoryPlanningItemDao.findByPlanAndLeafAndYear(level1Planning, categoryLeaf,
                planYear);
    }

    // Main Control method
    public String generateTreeGrid(final String id, final String roleCode, final Operator operator) {
        StringBuffer gridDoc = new StringBuffer();
        gridDoc.append("<Grid><Cfg id='LevelOnePlanning' MainCol='A' ColMoving='0' Dragging='0' Sorting='0' Alternate='0'/>");

        Level1Planning level1Planning = level1PlanningDao.get(id);
        Category category = level1Planning.getCategory();
        CategoryNode categoryNode = category.getRoot();

        // 1. Add body to Grid
        generateBody(gridDoc, categoryNode, category, level1Planning, roleCode, operator);

        // 2. Add columns to Grid
        generateLefCols(gridDoc, category);

        // 3. Add columns to Grid
        generateCols(gridDoc, category, level1Planning.getStatus());

        // 4. Add Head to Grid
        generateHead(gridDoc, category);

        // Get bureau and funding mode
        Map<String, String> bureauMap = new HashMap<String, String>();
        Map<String, String> fundingModeMap = new HashMap<String, String>();
        getBureauAndFundingMode(bureauMap, fundingModeMap, category.getRoot());

        // 5. Add Def to Grid
        generateDef(gridDoc, category, bureauMap, fundingModeMap);

        // 5. Add Def to Grid
        generateFoot(gridDoc, category, bureauMap, fundingModeMap);

        gridDoc.append("<Toolbar Visible='0'/>");
        gridDoc.append("<Panel Copy='0' Move='0' Select='0' Delete='0'/></Grid>");
        return gridDoc.toString();
    }

    /**
     * 
     * 
     * @param bureauMap
     * @param fundingModeMap
     * @param categoryNode
     */
    public void getBureauAndFundingMode(final Map<String, String> bureauMap,
            final Map<String, String> fundingModeMap, final CategoryNode categoryNode) {

        if (categoryNode.getIsLeafNode() == 1) {
            CategoryLeaf leaf = categoryLeafDao.get(categoryNode.getId());
            String fundingModeLabel = paramService.getFundingModeLabel(leaf.getFundingMode());

            bureauMap.put(leaf.getBureau(), leaf.getBureau());
            fundingModeMap.put(fundingModeLabel, fundingModeLabel);

            return;
        }

        if (categoryNode.getChildren().size() <= 0) {
            return;
        }

        for (CategoryNode child : categoryNode.getChildren()) {
            getBureauAndFundingMode(bureauMap, fundingModeMap, child);
        }

    }

    private void generateFoot(final StringBuffer gridDoc, final Category category,
            final Map<String, String> bureauMap, final Map<String, String> fundingModeMap) {
        StringBuilder foot = new StringBuilder();

        foot.append("<Foot>");
        // 1. Total
        foot.append("<I Def='Tree' A='&lt;B&gt;Total&lt;/B&gt;' AType='Html' CanEdit='0' />");

        // 2. by Founding Mode
        foot.append("<I A='By Funding Mode :' AType='Text' CanEdit='0' ");

        Integer level1Start = category.getLevel1Start();
        Integer level1End = category.getLevel1End();

        for (Integer i = level1Start; i <= level1End; i++) {
            String prefix = "_" + i + "_" + (i + 1) + "_";

            foot.append(prefix + "1Type='Text' ");
            foot.append(prefix + "2Type='Text' ");
            foot.append(prefix + "3Type='Text' ");
            foot.append(prefix + "4Type='Text' ");
        }
        foot.append("/>");

        Set<String> fundingModeS = fundingModeMap.keySet();
        for (String fundingMode : fundingModeS) {
            foot.append("<I Def='FF" + fundingMode + "' C='" + fundingMode + "' />");
        }

        // 3. by Bureau
        foot.append("<I A='By Bureau :' AType='Text' CanEdit='0' ");

        for (Integer i = level1Start; i <= level1End; i++) {
            String prefix = "_" + i + "_" + (i + 1) + "_";

            foot.append(prefix + "1Type='Text' ");
            foot.append(prefix + "2Type='Text' ");
            foot.append(prefix + "3Type='Text' ");
            foot.append(prefix + "4Type='Text' ");
        }

        foot.append("/>");

        Set<String> bureaus = bureauMap.keySet();
        for (String bureau : bureaus) {
            foot.append("<I Def='BB" + bureau + "' C='" + bureau + "' />");
        }

        foot.append("</Foot>");

        log.debug("Foot ={}", foot.toString());
        gridDoc.append(foot.toString());
    }

    private void generateLefCols(final StringBuffer gridDoc, final Category category) {
        String leftCols = "<LeftCols><C Name='A' Width='190' Type='Text' CanEdit='0' />"
                + "<C Name='B' Width='60' Type='Text' CanEdit='0' Align='Center' />"
                + "<C Name='C' Width='60' Type='Text' CanEdit='0' Align='Center' />"
                + "<C Name='DButton' Width='65' Type='Html' CanEdit='0' Align='Center' />"
                + "</LeftCols>";

        gridDoc.append(leftCols);
    }

    private void generateDef(final StringBuffer gridDoc, final Category category,
            final Map<String, String> bureauMap, final Map<String, String> fundingModeMap) {
        StringBuffer treeSb = new StringBuffer();

        Integer level1Start = category.getLevel1Start();
        Integer level1End = category.getLevel1End();
        Integer planYear = category.getPlanYear();

        treeSb.append("<D Name='Tree' Calculated='1' ");

        for (Integer i = level1Start; i <= level1End; i++) {
            String prefix = "_" + i + "_" + (i + 1) + "_";

            treeSb.append(prefix + "2Formula='sum()' ");
            treeSb.append(prefix + "3Formula='sum()' ");
            treeSb.append(prefix + "4Formula='sum()' ");
        }

        treeSb.append("/>");

        StringBuffer leafSb = new StringBuffer();
        recursionDefOfLeaf(leafSb, category, category.getRoot());

        // calculate bureau and funding mode
        StringBuffer defSb = new StringBuffer();

        Set<String> bureaus = bureauMap.keySet();
        for (String bureau : bureaus) {

            defSb.append("<D Name='BB" + bureau + "' Calculated='1' ");
            for (Integer i = level1Start; i <= level1End; i++) {
                String prefix = "_" + i + "_" + (i + 1) + "_";

                defSb.append(prefix + "2Formula=\"sum('" + prefix + "2','B==&quot;" + bureau
                        + "&quot;',4)\" ");

                defSb.append(prefix + "3Formula=\"sum('" + prefix + "3','B==&quot;" + bureau
                        + "&quot;',4)\" ");

                if (i.intValue() <= planYear.intValue()) {
                    defSb.append(prefix + "4Formula=\"sum('" + prefix + "4','B==&quot;" + bureau
                            + "&quot;',4)\" ");
                }
            }

            defSb.append("/>");
        }
        Set<String> fundingModes = fundingModeMap.keySet();
        for (String fundingMode : fundingModes) {

            defSb.append("<D Name='FF" + fundingMode + "' Calculated='1' ");
            for (Integer i = level1Start; i <= level1End; i++) {
                String prefix = "_" + i + "_" + (i + 1) + "_";

                defSb.append(prefix + "2Formula=\"sum('" + prefix + "2','C==&quot;" + fundingMode
                        + "&quot;',4)\" ");

                defSb.append(prefix + "3Formula=\"sum('" + prefix + "3','C==&quot;" + fundingMode
                        + "&quot;',4)\" ");

                if (i.intValue() <= planYear.intValue()) {
                    defSb.append(prefix + "4Formula=\"sum('" + prefix + "4','C==&quot;"
                            + fundingMode + "&quot;',4)\" ");
                }
            }
            defSb.append("/>");
        }

        String def = "<Def>" + treeSb.toString() + leafSb.toString() + defSb.toString() + "</Def>";
        log.debug("Def ={}", def);
        gridDoc.append(def);
    }

    private void recursionDefOfLeaf(final StringBuffer leafSb, final Category category,
            final CategoryNode categoryNode) {

        if (categoryNode.getIsLeafNode() == 0) {

            for (CategoryNode child : categoryNode.getChildren()) {
                recursionDefOfLeaf(leafSb, category, child);
            }

        } else {
            CategoryLeaf categoryLeaf = categoryLeafDao.get(categoryNode.getId());

            // Generate the grid, important method in this module
            Integer level1Start = category.getLevel1Start();
            Integer level1End = category.getLevel1End();
            Integer planYear = category.getPlanYear();

            leafSb.append("<D Name='FOR_" + categoryNode.getId() + "' Calculated='1' ");
            for (Integer i = level1Start; i <= level1End; i++) {
                String prefix = "_" + i + "_" + (i + 1) + "_";

                if (i.intValue() >= planYear.intValue()) {
                    if (categoryLeaf.getInputType() == InputType.TrainingHours) {
                        leafSb.append(prefix + "2Formula='" + prefix + "3/" + prefix + "1' ");
                    } else if (categoryLeaf.getInputType() == InputType.PlannedPlaces) {
                        leafSb.append(prefix + "3Formula='" + prefix + "2*" + prefix + "1' ");
                    }

                }

            }
            leafSb.append("/>");

        }

    }

    private void generateHead(final StringBuffer gridDoc, final Category category) {

        StringBuilder header1 = new StringBuilder();
        StringBuilder header2 = new StringBuilder();

        Integer level1Start = category.getLevel1Start();
        Integer level1End = category.getLevel1End();
        Integer planYear = category.getPlanYear();

        header1.append("<Header Spanned='1' ASpan='4' A='Planning Exercise : " + planYear + "/"
                + DateUtil.formatYear((planYear + 1), "yy") + "' AType='Html' ");

        header2.append("<Header id='Header' A='Programme' AAlign='Left' AType='Html' B='Bureau' BAlign='Center' BType='Html' C='Funding&lt;br/&gt;Mode' CAlign='Center' CType='Html' DButton='Discipline&lt;br/&gt;View' DButtonAlign='Center' DButtonType='Html' ");

        for (Integer i = level1Start; i <= level1End; i++) {
            String prefix = "_" + i + "_" + (i + 1) + "_";
            String headName = i + "-" + (i + 1);

            if (i > planYear) {

                header1.append(prefix + "1Span='3' " + prefix + "1='" + headName + "' " + prefix
                        + "1Align='Center' " + prefix + "1Type='Html' ");

                header2.append(prefix + "1=\"Conversion&lt;br/&gt;Factor\" " + prefix
                        + "1Align='Center' " + prefix + "1Type='Html' ");

                header2.append(prefix + "2=\"Planned&lt;br/&gt;Places\" " + prefix
                        + "2Align='Center' " + prefix + "2Type='Html' ");

                header2.append(prefix + "3=\"Planned&lt;br/&gt;Training&lt;br/&gt;Hours\" "
                        + prefix + "3Align='Center' " + prefix + "3Type='Html' ");

            } else if (i <= planYear) {

                header1.append(prefix + "1Span='4' " + prefix + "1='" + headName + "' " + prefix
                        + "1Align='Center' " + prefix + "1Type='Html' ");

                header2.append(prefix + "1=\"Conversion&lt;br/&gt;Factor\" " + prefix
                        + "1Align='Center' " + prefix + "1Type='Html' ");

                header2.append(prefix + "2=\"Planned&lt;br/&gt;Places\" " + prefix
                        + "2Align='Center' " + prefix + "2Type='Html' ");

                header2.append(prefix + "3=\"Planned&lt;br/&gt;Training&lt;br/&gt;Hours\" "
                        + prefix + "3Align='Center' " + prefix + "3Type='Html' ");

                header2.append(prefix + "4=\"Actual&lt;br/&gt;Enrolment\" " + prefix
                        + "4Align='Center' " + prefix + "4Type='Html' ");
            }
        }

        header1.append("/>");
        header2.append("/>");

        String head = "<Head>" + header1.toString() + header2.toString() + "</Head>";
        log.debug("Head ={}", head);
        gridDoc.append(head);

    }

    private void generateCols(final StringBuffer gridDoc, final Category category,
            final PlanningStatus planningStatus) {
        StringBuilder sb = new StringBuilder();

        Integer level1Start = category.getLevel1Start();
        Integer level1End = category.getLevel1End();
        Integer planYear = category.getPlanYear();

        sb.append("<Cols>");

        // planning status must control edit
        String canEdit = "0";
        if (planningStatus.getCanEditLevel1()) {
            canEdit = "1";
        }

        for (Integer i = level1Start; i <= level1End; i++) {
            String prefix = "_" + i + "_" + (i + 1) + "_";

            if (i > planYear) {
                sb.append("<C Name='" + prefix
                        + "1' Width='90' Type='Int' Align='Center' CanEdit='0'/>");

                sb.append("<C Name='" + prefix
                        + "2' Width='90' Type='Int' Align='Center' CanEdit='" + canEdit + "'/>");

                sb.append("<C Name='" + prefix
                        + "3' Width='90' Type='Int' Align='Center' CanEdit='" + canEdit + "'/>");

            } else if (i.intValue() == planYear.intValue()) {
                sb.append("<C Name='" + prefix
                        + "1' Width='90' Type='Int' Align='Center' CanEdit='0'/>");

                sb.append("<C Name='" + prefix
                        + "2' Width='90' Type='Int' Align='Center' CanEdit='" + canEdit + "'/>");

                sb.append("<C Name='" + prefix
                        + "3' Width='90' Type='Int' Align='Center' CanEdit='" + canEdit + "'/>");

                sb.append("<C Name='" + prefix
                        + "4' Width='90' Type='Int' Align='Center' CanEdit='" + canEdit + "'/>");

            } else if (i <= (planYear - 1)) {
                sb.append("<C Name='" + prefix
                        + "1' Width='90' Type='Int' Align='Center' CanEdit='0'/>");

                sb.append("<C Name='" + prefix
                        + "2' Width='90' Type='Int' Align='Center' CanEdit='0'/>");

                sb.append("<C Name='" + prefix
                        + "3' Width='90' Type='Int' Align='Center' CanEdit='0'/>");

                sb.append("<C Name='" + prefix
                        + "4' Width='90' Type='Int' Align='Center' CanEdit='" + canEdit + "'/>");
            }

        }

        sb.append("</Cols>");

        log.debug("Cols ={}", sb.toString());
        gridDoc.append(sb.toString());

    }

    // One
    private void generateBody(final StringBuffer gridDoc, final CategoryNode categoryNode,
            final Category category, final Level1Planning level1Planning, final String roleCode,
            final Operator operator) {
        StringBuilder sb = new StringBuilder();

        sb.append("<Body><B>");
        generateTree(categoryNode, sb, category, level1Planning, roleCode, operator);
        sb.append("</B></Body>");

        gridDoc.append(sb.toString());

    }

    // Two
    private void generateTree(final CategoryNode categoryNode, final StringBuilder sb,
            final Category category, final Level1Planning level1Planning, final String roleCode,
            final Operator operator) {

        // If operator is Planner, he can't edit some fields
        boolean canDisplayNode = true;
        if (operator == Operator.PLANNER) {
            long cnt = categoryPlannerDao.countBranchTreesByYear(categoryNode.getId(), roleCode);

            if (cnt <= 0) {
                canDisplayNode = false;
            }
        }

        // Discriminate the view discipline button will display
        String button = "";
        Integer isDisciplineView = categoryNode.getIsDisciplineView();
        if (isDisciplineView != null && isDisciplineView == 1) {
            button = "DButton='&lt;input type=&quot;button&quot; class=&quot;dButton&quot; onclick=&quot;toDisView(&apos;"
                    + level1Planning.getId()
                    + "&apos;,&apos;"
                    + categoryNode.getId()
                    + "&apos;)&quot; value=&quot;View&quot; /&gt;' ";
        }

        if (categoryNode.getIsLeafNode() == 0) {

            if (canDisplayNode) {
                sb.append("<I Def='Tree' id='" + categoryNode.getName() + "' A='"
                        + categoryNode.getName() + "' " + button + ">");
            }

            List<CategoryNode> children = categoryNode.getChildren();
            for (CategoryNode child : children) {
                generateTree(child, sb, category, level1Planning, roleCode, operator);
            }

            if (canDisplayNode) {
                sb.append("</I>");
            }

        } else {
            CategoryLeaf categoryLeaf = categoryLeafDao.get(categoryNode.getId());

            // Generate the grid, important method in this module
            String grid = generateGrid(categoryLeaf, category, level1Planning);

            if (canDisplayNode) {
                sb.append("<I Def='FOR_" + categoryNode.getId() + "' id='" + level1Planning.getId()
                        + "_" + categoryNode.getId() + "' A='" + categoryNode.getName() + "' B='"
                        + categoryLeaf.getBureau() + "' C='"
                        + paramService.getFundingModeLabel(categoryLeaf.getFundingMode()) + "' "
                        + button + grid + "></I>");
            }
        }

    }

    // Three
    private String generateGrid(final CategoryLeaf categoryLeaf, final Category category,
            final Level1Planning level1Planning) {

        StringBuilder sb = new StringBuilder();

        int canEditActualEnrolment = 0;
        if (categoryLeaf.getSourceType() == SourceType.ManualInput) {
            canEditActualEnrolment = 1;
        }

        int canEditPlannedPlace = 0;
        int canEditTrainingHours = 0;
        if (categoryLeaf.getInputType() == InputType.PlannedPlaces) {
            canEditPlannedPlace = 1;
        }

        if (categoryLeaf.getInputType() == InputType.TrainingHours) {
            canEditTrainingHours = 1;
        }

        if (categoryLeaf.getInputType() == InputType.Both) {
            canEditPlannedPlace = 1;
            canEditTrainingHours = 1;
        }

        Integer level1Start = category.getLevel1Start();
        Integer level1End = category.getLevel1End();
        Integer planYear = category.getPlanYear();

        for (Integer year = level1Start; year <= level1End; year++) {
            String prefix = "_" + year + "_" + (year + 1) + "_";

            CategoryPlanningItem item = generateDBData(categoryLeaf, level1Planning, planYear, year);

            if (year > planYear) {
                sb.append(prefix + "1='" + item.getConvertRate() + "' " + prefix + "1CanEdit='1' ");

                sb.append(prefix + "2='" + item.getPlannedPlaces() + "' " + prefix + "2CanEdit='"
                        + canEditPlannedPlace + "' ");

                sb.append(prefix + "3='" + item.getPlanedTrainingHours() + "' " + prefix
                        + "3CanEdit='" + canEditTrainingHours + "' ");

            } else if (year.intValue() == planYear.intValue()) {

                sb.append(prefix + "1='" + item.getConvertRate() + "' " + prefix + "1CanEdit='1' ");

                sb.append(prefix + "2='" + item.getPlannedPlaces() + "' " + prefix + "2CanEdit='"
                        + canEditPlannedPlace + "' ");

                sb.append(prefix + "3='" + item.getPlanedTrainingHours() + "' " + prefix
                        + "3CanEdit='" + canEditTrainingHours + "' ");

                sb.append(prefix + "4='" + item.getActualEnrolment() + "' " + prefix + "4CanEdit='"
                        + canEditActualEnrolment + "' ");

            } else if (year.intValue() < planYear.intValue()) {

                sb.append(prefix + "1='" + item.getConvertRate() + "' " + prefix + "1CanEdit='0' ");

                sb.append(prefix + "2='" + item.getPlannedPlaces() + "' " + prefix
                        + "2CanEdit='0' ");

                sb.append(prefix + "3='" + item.getPlanedTrainingHours() + "' " + prefix
                        + "3CanEdit='0' ");

                sb.append(prefix + "4='" + item.getActualEnrolment() + "' " + prefix + "4CanEdit='"
                        + canEditActualEnrolment + "' ");
            }

        }

        return sb.toString();
    }

    // Four
    // Four
    private CategoryPlanningItem generateDBData(final CategoryLeaf categoryLeaf,
            final Level1Planning level1Planning, final Integer planYear, final Integer year) {

        CategoryPlanningItem categoryPlanningItem = categoryPlanningItemDao
                .findByPlanAndLeafAndYear(level1Planning, categoryLeaf, year);

        if (categoryPlanningItem == null) {
            categoryPlanningItem = new CategoryPlanningItem();
            categoryPlanningItem.setLevel1Planning(level1Planning);
            categoryPlanningItem.setCategoryLeaf(categoryLeaf);
            categoryPlanningItem.setPlanYear(year);

            if (year >= planYear) {

                categoryPlanningItem.setPlanedTrainingHours(0);
                categoryPlanningItem.setPlannedPlaces(0);
                // TODO by node page input
                categoryPlanningItem.setActualEnrolment(0);

                // TODO default rate=?
                categoryPlanningItem.setConvertRate(1f);

            } else {

                // 1. find the history category
                Category oldMasterTree = null;
                try {
                    oldMasterTree = categoryService.findMasterTreeByPlanYear(year);
                } catch (Exception e) {
                    log.error(e.getMessage(), e);
                }

                // 2. find the history node
                CategoryNode oldCategoryNode = null;
                Level1Planning oldLevel1Planning = null;
                if (oldMasterTree != null) {
                    // 2. find the history level1Planning owned by old category
                    oldLevel1Planning = level1PlanningDao.findEndorsedByMasterTree(oldMasterTree);
                    List<String> path = categoryNodeService.getTreePathOfNode(categoryLeaf);
                    oldCategoryNode = categoryNodeService.getNodeByPath(path, oldMasterTree);
                }

                // 2. find history item
                if (oldLevel1Planning != null && oldCategoryNode != null) {
                    CategoryLeaf oldLeaf = categoryNodeService
                            .findLeafById(oldCategoryNode.getId());
                    CategoryPlanningItem oldCategoryPlanningItem = categoryPlanningItemDao
                            .findByPlanAndLeafAndYear(oldLevel1Planning, oldLeaf, year);

                    categoryPlanningItem.setPlanedTrainingHours(oldCategoryPlanningItem
                            .getPlanedTrainingHours());
                    categoryPlanningItem.setPlannedPlaces(oldCategoryPlanningItem
                            .getPlannedPlaces());

                    // TODO by node page input
                    categoryPlanningItem.setActualEnrolment(oldCategoryPlanningItem
                            .getActualEnrolment());
                    categoryPlanningItem.setConvertRate(oldCategoryPlanningItem.getConvertRate());

                } else {

                    categoryPlanningItem.setPlanedTrainingHours(0);
                    categoryPlanningItem.setPlannedPlaces(0);

                    // TODO by node page input
                    categoryPlanningItem.setActualEnrolment(0);
                    categoryPlanningItem.setConvertRate(0f);
                }
            }

            categoryPlanningItemDao.save(categoryPlanningItem);
        }

        return categoryPlanningItem;
    }

    public void save(final String treeGridData) {
        log.info("XML = {}", treeGridData);
        Iterator<Element> ite;

        try {
            ite = GridDoc.parseChanges(treeGridData);
        } catch (DocumentException e) {
            log.error(e.getMessage(), e);
            return;
        }

        while (ite.hasNext()) {
            Element elemI = ite.next();

            if (!StringUtils.equals("1", elemI.attributeValue("Changed"))) {
                continue;
            }

            String id = elemI.attributeValue("id");
            log.info("id: {}", id);
            if (StringUtils.isBlank(id)) {
                continue;
            }

            String[] ids = id.split("_");
            Level1Planning level1Planning = level1PlanningDao.get(ids[0]);
            CategoryLeaf categoryLeaf = categoryLeafDao.get(ids[1]);

            Iterator<Attribute> attrs = elemI.attributeIterator();

            while (attrs.hasNext()) {
                Attribute attr = attrs.next();

                // 1. find column name，example _2011_2012_2, _2011_2012_3,
                // _2011_2012_4
                String cellName = attr.getName();

                if (!cellName.startsWith("_")) {
                    continue;
                }

                // 2. find<I id="" Changed="1" _2014_2015_3="25"/>
                // the value "25" of attribute _2014_2015_3
                String cellvalue = attr.getValue();

                BigDecimal cellvalueB = new BigDecimal(cellvalue);
                Integer cellvalueInt = cellvalueB.setScale(0, RoundingMode.UP).intValue();

                // 2. Get "2014" of attribute _2014_2015_3 in <I id=""
                // Changed="1" _2014_2015_3="25"/>
                String planYear = cellName.substring(1, cellName.indexOf("_", 1));

                // 3. Read the last letter "3" of attribute _2014_2015_3 in <I
                // id="" Changed="1" _2014_2015_3="25"/>
                char columnType = cellName.charAt(cellName.length() - 1);

                CategoryPlanningItem categoryPlanningItem = categoryPlanningItemDao
                        .findByPlanAndLeafAndYear(level1Planning, categoryLeaf,
                                Integer.valueOf(planYear));

                Float factor = null;

                switch (columnType) {
                case '1':
                    factor = Float.valueOf(cellvalue);

                    if (categoryLeaf.getInputType().equals(InputType.TrainingHours) && factor > 0) {
                        categoryPlanningItem.setConvertRate(factor);

                        Integer plannedPlaces = calcPlannedPlaces(factor,
                                categoryPlanningItem.getPlanedTrainingHours());
                        categoryPlanningItem.setPlannedPlaces(plannedPlaces);

                    } else if (categoryLeaf.getInputType().equals(InputType.PlannedPlaces)) {
                        categoryPlanningItem.setConvertRate(factor);

                        Integer trainingHours = calcTrainingHours(factor,
                                categoryPlanningItem.getPlannedPlaces());

                        categoryPlanningItem.setPlanedTrainingHours(trainingHours);
                    }

                    break;

                case '2':
                    categoryPlanningItem.setPlannedPlaces(cellvalueInt);

                    // TODO 'input' type according to the exercise year
                    if (categoryLeaf.getInputType().equals(InputType.PlannedPlaces)) {
                        factor = categoryPlanningItem.getConvertRate();

                        Integer trainingHours = calcTrainingHours(factor,
                                categoryPlanningItem.getPlannedPlaces());

                        categoryPlanningItem.setPlanedTrainingHours(trainingHours);

                    }

                    break;

                case '3':
                    categoryPlanningItem.setPlanedTrainingHours(cellvalueInt);

                    factor = categoryPlanningItem.getConvertRate();

                    // TODO 'input' type according to the exercise year
                    if (categoryLeaf.getInputType().equals(InputType.TrainingHours)) {

                        if (factor > 0) {
                            Integer plannedPlaces = calcPlannedPlaces(factor,
                                    categoryPlanningItem.getPlanedTrainingHours());
                            categoryPlanningItem.setPlannedPlaces(plannedPlaces);
                        }
                    }

                    break;

                case '4':
                    categoryPlanningItem.setActualEnrolment(cellvalueInt);
                    break;
                }

                categoryPlanningItemDao.save(categoryPlanningItem);
            }

        }
    }

    private Integer calcPlannedPlaces(final Float factor, final Integer trainingHours) {
        BigDecimal plannedPlaces = new BigDecimal(trainingHours / factor);

        return plannedPlaces.setScale(0, RoundingMode.UP).intValue();
    }

    private Integer calcTrainingHours(final Float factor, final Integer plannedPlaces) {
        BigDecimal trainingHours = new BigDecimal(plannedPlaces * factor);

        return trainingHours.setScale(0, RoundingMode.UP).intValue();
    }

}
